-- EXEC SP_FETCH_FAB_ASSM_LOCATIONS 355, 'ASSEMBLY'

ALTER PROC SP_FETCH_FAB_ASSM_LOCATIONS( @CusomerShippingId INT, @Process Varchar(50) ) AS
BEGIN

	DECLARE @PRODUCT_CURSOR CURSOR
	DECLARE @Location_Cursor CURSOR
	DECLARE @Location_String_Cursor CURSOR 
	DECLARE @Location VARCHAR(50)
	DECLARE @COUNTRY VARCHAR(50)
	DECLARE @Location_String VARCHAR(300)
	DECLARE @ProcessStep_Id INT

	DECLARE @temp TABLE ( Location VARCHAR(50) )

	SET @Location_String = ''

	IF ( @Process = 'FAB' )
	BEGIN
		SET @PRODUCT_CURSOR = CURSOR FOR SELECT DISTINCT PROCESSSTEP_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND CUSTOMERSHIPPING_ID = @CusomerShippingId
		OPEN @PRODUCT_CURSOR
		FETCH NEXT FROM @PRODUCT_CURSOR INTO @ProcessStep_Id
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @Location_Cursor = CURSOR FOR SELECT COUNTRY FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L, ADDRESS A WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND L_TYPE = 'FAB' AND PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @ProcessStep_Id ) WHERE PS_TYPE = 'FAB' ) 
			OPEN @Location_Cursor
			FETCH NEXT FROM @Location_Cursor INTO @COUNTRY
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @Location_String = @Location_String + @COUNTRY + ','
			
				FETCH NEXT FROM @Location_Cursor INTO @COUNTRY
			END
			CLOSE @Location_Cursor
			DEALLOCATE @Location_Cursor
			
			FETCH NEXT FROM @PRODUCT_CURSOR INTO @ProcessStep_Id
		END
		CLOSE @PRODUCT_CURSOR
		DEALLOCATE @PRODUCT_CURSOR

		IF  LEN( @Location_String ) > 0 
		BEGIN
			SET @Location_String = SUBSTRING( @Location_String, 0, LEN( @Location_String )  )
		END
	END
	ELSE 
	BEGIN
		--SELECT @Location_String = COUNTRY FROM Address WHERE ADDRESS_ID IN ( SELECT DISTINCT ADDRESS_ID FROM LOCATION WHERE LOCATION_ID IN ( SELECT DISTINCT Location_Id FROM Vendor_Code_Table WHERE VENDOR_CODE IN ( SELECT DISTINCT ASSEMBLY_VENDOR FROM Inventory_lot WHERE Lot_ID IN ( SELECT DISTINCT Lot_Id FROM Customer_Shipping_lot_Number_table WHERE CSLIT_ID IN ( SELECT CSLIT_ID FROM Customer_Shipping_Line_Item_Table WHERE CustomerShipping_Id = @CusomerShippingId ) ) AND ASSEMBLY_VENDOR IS NOT NULL ) ) )
		SELECT DISTINCT @Location_String = COUNTRY FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, INVENTORY_LOT IL, VENDOR_CODE_TABLE VCT, LOCATION L, ADDRESS A WHERE CSLIT.CSLIT_ID = CSLNT.CSLIT_ID AND CSLNT.LOT_ID = IL.LOT_ID AND IL.ASSEMBLY_VENDOR = VCT.VENDOR_CODE AND VCT.LOCATION_ID = L.LOCATION_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND ASSEMBLY_VENDOR IS NOT NULL AND CUSTOMERSHIPPING_ID = @CusomerShippingId
	END

	
	SELECT @Location_String AS l_VENDOR_NAME
END